﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml
**	Desc : 종류별 운동내용을 Insert/Update/Delete한다. - XML
**	Test Exec Query : 아래 참고
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING_PARTS.cs
**	Program ID : Fit1011m
**	Auth : 송시명
**	Date : 2014-05-21
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml]
@ls_training_id nvarchar(10),
@ls_xml nvarchar(max) =  '<UPDMS></UPDMS>' --루트노드

AS
BEGIN

--exec usp_UPDMS_SYS_SP_LOG_Insert 'Fit1011m', 0, 'usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml', @ls_xml, @ls_training_id,'','','smstyle'

DECLARE @iXmlHandle int
EXEC sp_xml_preparedocument @iXmlHandle  OUTPUT, @ls_xml

--저장
INSERT INTO UPDMS_FIT_WEIGHT_TRAINING_PARTS (
       Training_Id,
       Training_Type_Id,
       Action_Weight,
       Action_Count,
       Set_Count,
       Remark,
       Create_User,
       Create_Dt,
       Update_User,
       Update_Dt
)
SELECT Training_Id,
       Training_Type_Id,
       Action_Weight,
       Action_Count,
       Set_Count,
       Remark,
       Create_User,
       GETDATE(),
       Update_User,
       GETDATE()
  FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_FIT_WEIGHT_TRAINING_PARTS', 2)
  WITH (
       Seq int,
       Training_Id nvarchar(10),
       Training_Type_Id nvarchar(5),
       Action_Weight decimal(10, 1),
       Action_Count int,
       Set_Count int,
       Remark nvarchar(max),
       Create_User nvarchar(20),
       Create_Dt datetime,
       Update_User nvarchar(20),
       Update_Dt datetime
       )
 WHERE Seq = 0


--수정
UPDATE UPDMS_FIT_WEIGHT_TRAINING_PARTS
   SET Training_Id = c.Training_Id,
       Training_Type_Id = c.Training_Type_Id,
       Action_Weight = c.Action_Weight,
       Action_Count = c.Action_Count,
       Set_Count = c.Set_Count,
       Remark = c.Remark,
       Update_User = c.Update_User,
       Update_Dt = GETDATE()
  FROM (
       SELECT b.Seq,
              b.Training_Id,
              b.Training_Type_Id,
              b.Action_Weight,
              b.Action_Count,
              b.Set_Count,
              b.Remark,
              b.Update_User
         FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS AS a
         JOIN (
              SELECT Seq,
                     Training_Id,
                     Training_Type_Id,
                     Action_Weight,
                     Action_Count,
                     Set_Count,
                     Remark,
                     Save_Yn,
                     Update_User
                FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_FIT_WEIGHT_TRAINING_PARTS', 2)
                WITH (
                     Seq int,
                     Training_Id nvarchar(10),
                     Training_Type_Id nvarchar(5),
                     Action_Weight decimal(10, 1),
                     Action_Count int,
                     Set_Count int,
                     Remark nvarchar(max),
                     Save_Yn nvarchar(1),
                     Update_User nvarchar(20)
                     )
               WHERE Seq <> 0 
                 AND Save_Yn <> '-'
              ) b
           ON a.Seq = b.Seq
        WHERE a.Training_Id = b.Training_Id
          AND a.Training_Type_Id = b.Training_Type_Id
          AND ( a.Action_Weight <> b.Action_Weight
               OR a.Action_Count <> b.Action_Count
               OR a.Set_Count <> b.Set_Count
               OR a.Remark <> b.Remark ) -- 값이 다른것만 수정
       ) c
 WHERE UPDMS_FIT_WEIGHT_TRAINING_PARTS.Seq = c.Seq


--삭제
DELETE FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS
WHERE Seq IN (
        SELECT A.Seq
          FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS A WITH(NOLOCK)
          LEFT OUTER JOIN (
               SELECT Training_Id,
                      Training_Type_Id,
                      Action_Weight,
                      Action_Count,
                      Set_Count,
                      Remark
                 FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_FIT_WEIGHT_TRAINING_PARTS', 2)
                 WITH (
                      Seq int,
                      Training_Id nvarchar(10),
                      Training_Type_Id nvarchar(5),
                      Action_Weight decimal(10, 1),
                      Action_Count int,
                      Set_Count int,
                      Remark nvarchar(max)
                      )
               ) B
            ON A.Training_Id = B.Training_Id
               AND A.Training_Type_Id = B.Training_Type_Id
         WHERE A.Training_Id = @ls_training_id
           AND B.Training_Id IS NULL
       )


--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle

END

/*
--Test Exec Query--

DECLARE @ls_xml nvarchar(max) 
SET @ls_xml=''

EXEC usp_UPDMS_FIT_WEIGHT_TRAINING_PARTS_Insert_Update_Xml @ls_xml

*/